package com.jxc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.lang.reflect.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * 将数据集合导出到excel
 * @author PanJing
 *
 */
public class ExportToExcel{

	
	private List collection;
	private String filename;
	private String sheetName;
	private Object model;
	private String removes[]=new String[]{};
	private String[] heads=new String[]{};
	private File file;
	
	
	//请求对象和响应对象
	private HttpServletRequest request;
	private HttpServletResponse response;
	
	/**
	 * 设置表头
	 * @param heads
	 */
	public void setHeads(String[] heads) {
		this.heads = heads;
	}
	public String[] getHeads() {
		return heads;
	}
	
	/**
	 * 设置排除字段
	 * @param removes
	 */
	public void setRemoves(String[] removes) {
		this.removes = removes;
	}
	public String[] getRemoves() {
		return removes;
	}

	/**
	 * 
	 * @param collection	数据集合
	 * @param filename		文件名
	 * @param headsm		表头
	 * @param sheetName		表名
	 */
	public ExportToExcel(List collection,String filename,Object model,String sheetName,HttpServletRequest request,HttpServletResponse response){
		
		this.collection=collection;
		this.filename=filename;
		this.sheetName=sheetName;
		this.model=model;
		this.request=request;
		this.response=response;
		
		//获取服务器路径
		String path=request.getRealPath("/");
		path+="/tmp";
		
		File tmp=new File(path);
		if(!tmp.exists()){
			System.out.println(path);
			tmp.mkdirs();
		}
		
		this.file=new File(path+"/"+filename);
		if(!file.exists()){
			try {
				file.createNewFile();
				
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
		
	}
	
	/**
	 * 到导出
	 * @throws Exception
	 */
	public void export() throws Exception{
		
		
		WritableWorkbook book = Workbook.createWorkbook(this.file);
		//创建一张表
		WritableSheet sheet = book.createSheet(this.sheetName!=null?this.sheetName:"Sheet1", 0);
		
		//模型方法助手
		ModelUtil util=new ModelUtil(this.model,this.removes);
		
		//写入表头
		
		//如果没有设置表头就用对象默认的字段
		if(heads.length<=0){
		
			
			List heads=util.getHeads();
			
			for (int i = 0; i < heads.size(); i++) {
				
				Label label=new Label(i,0,heads.get(i).toString());
				
				sheet.addCell(label);
				
			}
			
		}else {
			
			for (int i = 0; i < heads.length; i++) {
				
				Label label=new Label(i,0,heads[i].toString());
				
				sheet.addCell(label);
				
			}
			
		}
		
		
		
		//写入数据
		
		try{
			
			for(int i=1;i<collection.size();i++){
				
				Object object=collection.get(i);
				
				List vList=util.getValues(object);
				
				for(int j=0;j<vList.size();j++){ //错误位置
					
						Object object2=vList.get(j);
				
						Label label=new Label(j,i,(String)object2);
						sheet.addCell(label);
				
				}
				
			}
			
			
			
		}catch (Exception e) {
			System.err.println("175 line error");
		}
		finally{
		
		
		book.write();
		book.close();
		
		//将文件通过文件流的方式输出到客户端
		//--
		//设置response对象的相关信息
		
		//输出类型
		response.setContentType("application/x-msdownload");
		
		//文件大小
		response.setContentLength((int) file.length());	
		
		//文件名
		response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), "iso-8859-1"));

		   // 从response对象中得到输出流,准备下载
		   ServletOutputStream myout = response.getOutputStream();
		   FileInputStream stream=new FileInputStream(file);
		   byte data[]=new byte[4096];
		   int size=0;
		   size=stream.read(data);
		  
		   // 开始循环写出数据
		   while (size!=-1) {
		    myout.write(data,0,size);
		    size=stream.read(data);
		   }
		   
		   myout.flush();
		   myout.close();
		   stream.close();
		   
		   //删除文件
		   //file.delete();
		
		//--
		}
	}
	
	
	
	/**
	 * 调用该方法清除缓存
	 */
	public void close(){
		
		//文件输出到客户端以后就删除改文件
		file.delete();
		 
	}
}

/**
 * 反射对象的取值
 * @author Administrator
 *
 */
class ModelUtil{

	
	private Object object;
	
	private Field[] fields;
	private Method[] methods;
	private String [] removes;
	private Class cls;
	
	public ModelUtil(Object object,String[] removes){
		this.object=object;
		
		this.cls=object.getClass();
		
		this.fields=cls.getDeclaredFields();
		this.methods=cls.getMethods();
		this.removes=removes;
		
	}
	
	/**
	 * 设置对象
	 * @param object
	 */
	public void setObject(Object object) {
		this.object = object;
	}
	public Object getObject() {
		return object;
	}
	
	/**
	 * 获取值集合
	 * @return  
	 */
	public List getValues(Object obj){
		
		List ls=new ArrayList();

		if(obj==null){
			//ls.add("");
			return ls;
		}
		
		Method [] ms=obj.getClass().getDeclaredMethods();
		Field []fs=obj.getClass().getDeclaredFields();
		
		for (Field field : fs) {
			field.setAccessible(true);
			if(findByMethod(ms, field.getName())){
				
				if(!findRemoveName(field.getName())){
					
					try {
						Object value=field.get(obj);
						ls.add((String)value);
						
					}catch (Exception e) {
						System.err.println(e+"291");
					}
					
				}
			}
			
		}

		return ls;
		
	}
	
	/**
	 * 获取对象的所有public方法的值
	 * @param object
	 * @return
	 */
	public List getHeads(){
		List list=new ArrayList();
		
		for (Field field : fields) {
			
			field.setAccessible(true);
			
			try {
				
				if(findByMethod(methods,field.getName())){
					
					if(!findRemoveName(field.getName())){
						list.add(field.getName());
					}
				}
				
			} catch (Exception e) {
				System.err.println("323 ");
			}
			
			
		}
		
		return list;
		
	}
	
	
	/**
	 * 寻找字段对应的方法
	 * @param methods
	 * @param name
	 * @return
	 */
	private boolean findByMethod(Method[] methods,String name){
		
		for (Method method : methods) {
			
			String lable="get"+getUpperCase(name);
			
			if(method.getName().equals(lable)){
				return true;
			}
		}
		return false;
	}
	
	/**
	 * 首字母大写
	 * @param value
	 * @return
	 */
	public String getUpperCase(String value){
		
		return value.substring(0,1).toUpperCase()+value.substring(1);
		
	}
	
	/**
	 * 获取字段的值
	 * @param fields
	 * @param methods
	 * @param obj
	 * @return
	 */
	public List getFieldValues(){
		
		List list=new ArrayList();
		
		
		for (Field field:fields) {
			
			//设置字段可访问
			field.setAccessible(true);
			
			if(findByMethod(methods, field.getName())){
				try {
					
					if(!findRemoveName(field.getName()))
					list.add(field.get(object));
					
				}catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		
		return list;
	}
	
	/**
	 * 检查字段是否在排除列表
	 * @param fieldName
	 * @return
	 */
	private boolean findRemoveName(String fieldName){
		
		for(String name:removes){
			if(name.equals(fieldName)){
				return true;
			}
		}
		
		return false;
	}
}
